package grsu.epam.airline.DAOImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import grsu.epam.airline.DAO.IAirportInRouteDAO;
import grsu.epam.airline.DAO.IFlightCrewDAO;
import grsu.epam.airline.DAO.IFlightDAO;
import grsu.epam.airline.DAO.IPlaneDAO;
import grsu.epam.airline.model.AirportInRoute;
import grsu.epam.airline.model.Flight;
import grsu.epam.airline.model.FlightCrew;
import grsu.epam.airline.model.Plane;

public class FlightDAOImpl implements IFlightDAO {
	private static final String GET_ALL_FLIGHTS_QUERY = "select flight_id, flightcrew_id,plane_id from flight";
	private static final String DELETE_FLIGHT = "DELETE FROM flight WHERE flight_id=?";
	private static final String DELETE_FLIGHT_ROUTE = "DELETE FROM airportinroute WHERE flight_id=?";
	private static final String GET_FLIGHT_BY_ID_QUERY = "select flight_id,flightcrew_id,plane_id from flight where flight_id=?";
	private static final String UPDATE_FLIGHT = "update flight set flightcrew_id=?,plane_id=? where flight_id=?";
	private static final String ADD_FLIGHT = "insert into flight (flightcrew_id,plane_id) values (?,?)";

	private Connection conn;

	public FlightDAOImpl(Connection conn) {
		this.conn = conn;
	}

	@Override
	public Flight getFlightById(int flightId) throws SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Flight flight = null;
		try {
			stmt = conn.prepareStatement(GET_FLIGHT_BY_ID_QUERY);
			stmt.setInt(1, flightId);
			rs = stmt.executeQuery();
			while (rs.next()) {
				IAirportInRouteDAO routeDao = new AirportInRouteDAOImpl(conn);
				List<AirportInRoute> route = routeDao.getAirportsByFlightId(rs
						.getInt("flight_id"));
				IPlaneDAO planeDao = new PlaneDAOImpl(conn);
				Plane plane = planeDao.getPlaneById(rs.getInt("plane_id"));
				IFlightCrewDAO crewDao = new FlightCrewDAOImpl(conn);
				FlightCrew flightCrew = crewDao.getFlightCrewById(rs
						.getInt("flightcrew_id"));
				flight = new Flight(rs.getInt("flight_id"), plane, flightCrew,
						route);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return flight;
	}

	@Override
	public void deleteFlight(int flightId) throws SQLException {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(DELETE_FLIGHT);
			stmt.setInt(1, flightId);
			stmt.executeUpdate();
			stmt = conn.prepareStatement(DELETE_FLIGHT_ROUTE);
			stmt.setInt(1, flightId);
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
	}

	@Override
	public List<Flight> getAllFlight() throws SQLException {
		List<Flight> flights = new ArrayList<Flight>();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.prepareStatement(GET_ALL_FLIGHTS_QUERY);
			rs = stmt.executeQuery();
			while (rs.next()) {
				IPlaneDAO planeDAO = new PlaneDAOImpl(conn);
				Plane plane = planeDAO.getPlaneById(rs.getInt("plane_id"));
				IFlightCrewDAO flightCrewDAO = new FlightCrewDAOImpl(conn);
				FlightCrew flightCrew = flightCrewDAO.getFlightCrewById(rs
						.getInt("flightcrew_id"));
				IAirportInRouteDAO routeDAO = new AirportInRouteDAOImpl(conn);
				List<AirportInRoute> route = routeDAO.getAirportsByFlightId(rs
						.getInt("flight_id"));
				flights.add(new Flight(rs.getInt("flight_id"), plane,
						flightCrew, route));
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return flights;
	}

	@Override
	public Connection getConnection() {
		return this.conn;
	}

	@Override
	public void updateFlight(int flightId, int crewId, int planeId)
			throws SQLException {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(UPDATE_FLIGHT);
			stmt.setInt(1, crewId);
			stmt.setInt(2, planeId);
			stmt.setInt(3, flightId);
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
	}

	@Override
	public void addFlight(int crewId, int planeId) throws SQLException {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(ADD_FLIGHT);
			stmt.setInt(1, crewId);
			stmt.setInt(2, planeId);
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
	}

}
